using UnityEngine;
using System.Collections;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using Excel;
using OfficeOpenXml;
using System.Data;
using UnityEngine.UI;

public class NewBehaviourScript : MonoBehaviour 
{
    public Text readData;
	void Start () 
	{		
		XLSX();
	}
	
	void XLSX()
	{
        try
        {
            using (FileStream stream = File.Open(Application.dataPath + "/ExcelTest/UserLevel.xlsx", FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

                DataSet result = excelReader.AsDataSet();

                int columns = result.Tables[0].Columns.Count;
                int rows = result.Tables[0].Rows.Count;

                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < columns; j++)
                    {
                        string nvalue = result.Tables[0].Rows[i][j].ToString();
                        Debug.Log(nvalue);
                        if (i > 0)
                        {
                            readData.text += "\t\t" + nvalue;
                        }
                        else
                        {
                            readData.text += "   \t" + nvalue;
                        }
                    }
                    readData.text += "\n";
                }

                
            }
        }
        catch(Exception e)
        {
            Debug.Log(e.GetType());
        }

        WriteExcel(Application.dataPath + "/ExcelTest/UserLevel1.xlsx");
    }

    public static void WriteExcel(string outputDir)
    {
        //string outputDir = EditorUtility.SaveFilePanel("Save Excel", "", "New Resource", "xlsx");  
        FileInfo newFile = new FileInfo(outputDir);
        if (newFile.Exists)
        {
            newFile.Delete();  // ensures we create a new workbook  
            newFile = new FileInfo(outputDir);
        }
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            // add a new worksheet to the empty workbook  
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
            //Add the headers  
            worksheet.Cells[1, 1].Value = "ID";
            worksheet.Cells[1, 2].Value = "Product";
            worksheet.Cells[1, 3].Value = "Quantity";
            worksheet.Cells[1, 4].Value = "Price";
            worksheet.Cells[1, 5].Value = "Value";

            //Add some items...  
            worksheet.Cells["A2"].Value = 12001;
            worksheet.Cells["B2"].Value = "Nails";
            worksheet.Cells["C2"].Value = 37;
            worksheet.Cells["D2"].Value = 3.99;

            worksheet.Cells["A3"].Value = 12002;
            worksheet.Cells["B3"].Value = "Hammer";
            worksheet.Cells["C3"].Value = 5;
            worksheet.Cells["D3"].Value = 12.10;

            worksheet.Cells["A4"].Value = 12003;
            worksheet.Cells["B4"].Value = "Saw";
            worksheet.Cells["C4"].Value = 12;
            worksheet.Cells["D4"].Value = 15.37;

            //save our new workbook and we are done!  
            package.Save();
        }
    }

}
